package com.fy56.platform.goodsTracking.api.impl;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.JSONPObject;
import com.dexcoder.dal.JdbcDao;
import com.fy56.model.ResultModel;
import com.fy56.platform.goodsTracking.api.GoodsTrackApi;
import com.fy56.platform.goodsTracking.commons.DateUtil;
import com.fy56.platform.goodsTracking.commons.unit.SystemConstants;
import com.fy56.platform.goodsTracking.model.*;
import com.fy56.platform.goodsTracking.quartz.JrptUpload;
import com.fy56.platform.goodsTracking.service.GoodsTrackedSetingPublicService;
import com.fy56.platform.goodsTracking.service.JrptService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * Created by XTK on 2016-08-18.
 */
@Repository
public class GoodsTrackApiImpl implements GoodsTrackApi {
    @Autowired
    JdbcDao jdbcDao;

    @Autowired
    JdbcDao jdbcDao_GoodsTracked;

    @Autowired
    JrptUpload jrptExecutor;


    @Autowired
    JrptService jrptService;

    @Autowired
    GoodsTrackedSetingPublicService goodsTrackedSetingPublicService;

    private static final Logger log = LoggerFactory.getLogger(GoodsTrackApiImpl.class);

    @Override
    public void insert_extract(String dataSource, String databasename, String wxpublicnumber, String appid) {
        if (wxpublicnumber == null) {
            wxpublicnumber = "";
        }
        if (appid == null) {
            appid = "";
        }

        String datetime = DateUtil.Now();

        int numTmp = 0;
        String sqlArray0 = "";
        int i = 1;

        List<GoodsTrack> listByDelete = new ArrayList<GoodsTrack>();
        Jrpt jrpt;
        String upload;
        //更新金融平台代码
        List<GoodstrackedSeting> setingList = jdbcDao.queryListForSql("select Organizationcode, Info=isnull(stuff((select ','+'\"'+TYPE+'\"'+':'+'\"'+Info+'\"'  from GoodsTracked_Seting t \n" +
                "where  Type like '%JRPT%' and Organizationcode=GoodsTracked_Seting.Organizationcode for xml path('')), 1, 1, '') \n" +
                ",'空') from GoodsTracked_Seting \n" +
                "group by Organizationcode ", GoodstrackedSeting.class);

        for (GoodstrackedSeting seting : setingList) {
            jrpt = new Jrpt();
            if (!seting.getInfo().equals("") && !seting.getInfo().equals("空")) {
                try {
                    String info = "{" + seting.getInfo() + "}";
                    //log.info("DataSource:" + dataSource + ";Databasename:" + databasename + ";更新金融代码:" + info);
                    JSONObject jb = JSON.parseObject(info);
                    if (!jb.get("JRPT_User").equals("")) {
                        jrpt.setOrganizationcode(seting.getOrganizationcode());
                        jrpt.setJrptuser(String.valueOf(jb.get("JRPT_User")));
                        jrpt.setJrptpass(String.valueOf(jb.get("JRPT_Pass")));
                        upload = String.valueOf(jb.get("JRPT_Upload"));
                        // jrptService.delete(jrpt.getOrganizationcode());
                        if (upload.equals("1"))
                            jrptService.save(jrpt);
                    }
                } catch (Exception e) {
                    log.error("DataSource:" + dataSource + ";Databasename:" + databasename + ";更新金融平台代码失败:" + e.getMessage());
                }
            }
        }
        //货物跟踪数据
        Long begin;
        String insertSql;
        String seleclSql;
        Long end;

        //同步货运单数据
        ConsignmentBillMaster consignmentBillMaster = new ConsignmentBillMaster();
        List<ConsignmentBillMaster> listByConsignmentBillMasterDelete = new ArrayList<ConsignmentBillMaster>();

        begin = System.currentTimeMillis();
        insertSql = "insert into GoodsTracked_Record_Top_Public( " +
                "headquartersname,headquarterphone,databasename,consignorCompanySende,consigneeCompanySenderCode,createType"+
                ",ConsignmentLongitude,ConsignmentLatitude,ArrivalLongitude,ArrivalLatitude," +
                "SendLongitude,SendLatitude,PickAddress,PlatformNumber,ReceiveLongitude,ReceiveLatitude," +
                "Headquarterscode,headquarterspassword,arrivalcode,arrivalpassword,consignmentcode ,consignmentpassword," +
                "  OrganizationCode,ConsignmentBillMasterId,ConsignmentBillNumber,GoodsNumber,ManualNumber,ConsignmentBillDate,PlaceOfLoading,SendCompany,ReceiveCompany,UnloadPlace,ConsignorCompanyCode,ConsignorCompany,Consignor,ConsignorMobileTelephone,ConsignorTelephone,ConsignorAddress,ConsigneeCompanyCode,ConsigneeCompany, Consignee,ConsigneeMobileTelephone" +
                "  ,ConsigneeTelephone, ConsigneeAddress,Consignoridcard,Consigneeidcard,OneCardCode,BankName,AccountHolder,Account,PaymentMethod,GoodsPickupMethod,ConsignmentBillRemark,GoodsName, GoodsPack,IsReceipt,ReceiptNumber,ReceiptCount,ReceiptRemark,TotalNumberOfPackages, TotalWeight, TotalVolume,TotalTransportCost, CollectionGoodsValue,AdvanceGoodsValue,AdvanceTransportCost,DataType,SysTime)";
        seleclSql = " select '" +
                "'''+Isnull(z.CompanyName, '')+'''," +
                "'''+Isnull(z.CompanyPhoneNumber, '')+'''," +
                "''' +'"+databasename+"'+'''," +
                "'''+Isnull(c.FyCloudCode, '')+'''," +
                "'''+Isnull(dh.FyCloudCode, '')+'''," +
                "' +Isnull(convert(varchar(20),convert(decimal(18,0),createtype)), '0')+ '," +

                "'''+Isnull(c.Longitude, '')+'''," +
                "'''+Isnull(c.Latitude, '')+'''," +
                "'''+Isnull(dh.Longitude, '')+'''," +
                "'''+Isnull(dh.Latitude, '')+'''," +

                "'''+Isnull(CONVERT(varchar(200),t.SendLongitude), '')+'''," +
                "'''+Isnull(CONVERT(varchar(200),t.SendLatitude), '')+'''," +
                "'''+Isnull(t.PickAddress, '')+'''," +
                "'''+Isnull(t.PlatformNumber, '')+'''," +
                "'''+Isnull(CONVERT(varchar(200),t.ReceiveLongitude), '')+'''," +
                "'''+Isnull(CONVERT(varchar(200),t.ReceiveLatitude), '')+'''," +

                "'''+Isnull(z.FyCloudCode, '')+'''," +
                "'''+Isnull(z.FyCloudPassword, '')+'''," +
                "'''+Isnull(dh.FyCloudCode, '')+'''," +
                "'''+Isnull(dh.FyCloudPassword, '')+'''," +
                "'''+Isnull(c.FyCloudCode, '')+'''," +
                "'''+Isnull(c.FyCloudPassword, '')+''','''" +
                "+Isnull(t.OrganizationCode, '')+''','''+Isnull(ConsignmentBillMasterId, '')+''','''+Isnull(ConsignmentBillNumber, '')+''','''+Isnull(GoodsNumber, '')+''','''+Isnull(ManualNumber, '')+''','''+Isnull(CONVERT(varchar(100), ConsignmentBillDate, 20), '')+''','''+Isnull(PlaceOfLoading, '')+''','''+Isnull(SendCompany, '')+''','''" +
                " +Isnull(ReceiveCompany, '')+''','''+Isnull(UnloadPlace, '')+''','''+Isnull(ConsignorCompanyCode, '')+''','''+Isnull(ConsignorCompany, '')+''','''+Isnull(Consignor, '')+''','''+Isnull(ConsignorMobileTelephone, '')+''','''+Isnull(ConsignorTelephone, '')+''','''+Isnull(ConsignorAddress, '')+''','''" +
                " +Isnull(ConsigneeCompanyCode, '')+''','''+Isnull(ConsigneeCompany, '')+''','''+Isnull(Consignee, '')+''','''+Isnull(ConsigneeMobileTelephone, '')+''','''+Isnull(ConsigneeTelephone, '')+''','''+Isnull(ConsigneeAddress, '')+''','''+isnull(Consignoridcard,'')+''','''+isnull(Consigneeidcard,'')+''','''+Isnull(OneCardCode, '')+''','''+Isnull(BankName, '')+''','''" +
                " +Isnull(AccountHolder,'')+''','''+Isnull(Account,'')+''','''+Isnull(PaymentMethod,'')+''','''+Isnull(GoodsPickupMethod,'')+''','''+Isnull(ConsignmentBillRemark,'')+''','''+Isnull(GoodsName,'')+''','''+ Isnull(GoodsPack,'')+''','''+Isnull(IsReceipt,'')+''','''+Isnull(ReceiptNumber,'')+''','" +
                " +Isnull(convert(varchar(20),convert(decimal(18,0),ReceiptCount)), '0')+',''' +Isnull(ReceiptRemark,'')+''','+Isnull(convert(varchar(20),convert(decimal(18,0),TotalNumberOfPackages)),'0')+','" +
                " +Isnull(convert(varchar(20),convert(decimal(18,0),TotalWeight)), '0')+','+Isnull(convert(varchar(20),convert(decimal(18,0),TotalVolume)), '0')+','" +
                " +Isnull(convert(varchar(20),convert(decimal(18,0),TotalTransportCost)), '0')+',' + Isnull(convert(varchar(20),convert(decimal(18,0),CollectionGoodsValue)), '0')+',' +Isnull(convert(varchar(20),convert(decimal(18,0),AdvanceGoodsValue)), '0')+','" +
                " +Isnull(convert(varchar(20),convert(decimal(18,0),AdvanceTransportCost)), '0')+','+Isnull(convert(varchar(20),convert(decimal(18,0),DataType)), '0')+','''+Isnull(CONVERT(varchar(100), SysTime, 20), '') +'''' sqltext " +
                ",CONVERT(varchar(100), SysTime, 20) systime,convert(varchar(20),convert(decimal(18,0),DataType)) datatype" +
                " ,t.organizationcode,consignmentbillmasterid " +

                " from GoodsTracked_Record_Top t" +
                " left join Sys_Company c on t.OrganizationCode=c.OrganizationCode and t.SendCompany=c.CompanyName " +
                "    left join Sys_Company dh on t.OrganizationCode=dh.OrganizationCode and t.ReceiveCompany=dh.CompanyName " +
                "    left join Sys_Company z on t.OrganizationCode=z.OrganizationCode and z.CompanyType='总公司'"
                + "  where 1=1 and t.SysTime<='"+datetime+"'";
        String testSql=" and (t.datatype!='100' or t.datatype is null)";
        seleclSql=seleclSql+testSql;
//        + "  where t.SysTime<='" + datetime + "'";

        List<ConsignmentBillMaster> listByConsignmentBillMaster = null;

        try{
            listByConsignmentBillMaster = jdbcDao.queryListForSql(seleclSql, ConsignmentBillMaster.class);
        }catch (Exception e){
            log.error("DataSource:" + dataSource + ";Databasename:" + databasename +"货运单 error",e);
        }

        end = System.currentTimeMillis();
        log.info("DataSource:" + dataSource + ";Databasename:" + databasename + ";货运单数据：" + listByConsignmentBillMaster.size() + ";消耗" + (end - begin) + "豪秒。");
        //log.info("查询sql："+seleclSql);

        numTmp = 0;
        sqlArray0 = "";
        i = 1;
        begin = System.currentTimeMillis();
        String ConsignmentBillMasterIdStr = "";
        for (ConsignmentBillMaster s : listByConsignmentBillMaster) {

            if (ConsignmentBillMasterIdStr.isEmpty()){
                ConsignmentBillMasterIdStr="'"+s.getConsignmentbillmasterid()+"'";
            } else {
                ConsignmentBillMasterIdStr=ConsignmentBillMasterIdStr+","+"'"+s.getConsignmentbillmasterid()+"'";
            }

            if("ccchengquan".equals(s.getOrganizationcode())){
                log.error("ccchengquan:"+s.getSqltext());
            }
            if (s.getSqltext()==null||"null".equals(s.getSqltext())||"NULL".equals(s.getSqltext())||s.getSqltext().length()==0){
                log.error("订单查询异常null error id:"+s.getConsignmentbillmasterid());
            }else{

            }
            try {
                if (s.getDatatype().equals("0")) {
                    try {
                        jdbcDao_GoodsTracked.updateForSql(insertSql + " values " + "(" + s.getSqltext() + ")");
                    } catch (Exception e) {
                        log.info("货运单新增插入sql的值" + insertSql + " values " + "(" + s.getSqltext() + ")");
                        log.info("货运单新增插入异常ConsignmentBillMaster对象的值" + JSON.toJSONString(s) +"");
                        log.error("DataSource:" + dataSource + ";Databasename:" + databasename + ";货运单插入异常：" + s.getConsignmentbillmasterid() );
                        e.printStackTrace();
                    }
                } else if (s.getDatatype().equals("1")) {

                    //如果是修改  需要把原记录的唯一id取出  然后插入一条状态是修改的新记录 把原记录的唯一id更新到新记录上 然后把原记录删除
                    List<GoodstrackedRecordTopPublicF6> list=jdbcDao_GoodsTracked.queryListForSql("select * from GoodsTracked_Record_Top_Public where OrganizationCode='" + s.getOrganizationcode()+ "' and  ConsignmentBillMasterId='" + s.getConsignmentbillmasterid() + "'"  ,GoodstrackedRecordTopPublicF6.class);
                    jdbcDao_GoodsTracked.updateForSql("delete  from GoodsTracked_Record_Top_Public where OrganizationCode='" + s.getOrganizationcode()+ "' and  ConsignmentBillMasterId='" + s.getConsignmentbillmasterid() + "'");
                    try {
                        jdbcDao_GoodsTracked.updateForSql(insertSql + " values " + "(" + s.getSqltext() + ")");
                    } catch (Exception e) {
                        log.info("货运单修改插入sql的值" + insertSql + " values " + "(" + s.getSqltext() + ")");
                        log.info("货运单修改插入异常ConsignmentBillMaster对象的值" + JSON.toJSONString(s) +"");
                        log.error("DataSource:" + dataSource + ";Databasename:" + databasename + ";货运单插入异常：" + s.getConsignmentbillmasterid() );
                        e.printStackTrace();
                    }
                    if (list!=null && list.size()>0){
                        jdbcDao_GoodsTracked.updateForSql("update GoodsTracked_Record_Top_Public set TracePlatformId = '"+ list.get(0).getTraceplatformid() +"' where OrganizationCode='" + s.getOrganizationcode()+ "' and  ConsignmentBillMasterId='" + s.getConsignmentbillmasterid() + "'");
                    }

//                    consignmentBillMaster.setOrganizationcode(s.getOrganizationcode());
//                    consignmentBillMaster.setConsignmentbillmasterid(s.getConsignmentbillmasterid());
//                    consignmentBillMaster.setSystime(s.getSystime());
//                    listByConsignmentBillMasterDelete.add(consignmentBillMaster);
                } else if (s.getDatatype().equals("2")) {

                    //如果是删除  需要把原记录的状态更新为删除   然后不需要删除
                    jdbcDao_GoodsTracked.updateForSql("update GoodsTracked_Record_Top_Public set DataType = '2',uploadFlag='' where OrganizationCode='" + s.getOrganizationcode()+ "' and ConsignmentBillMasterId='" + s.getConsignmentbillmasterid() + "'");

//                    consignmentBillMaster.setOrganizationcode(s.getOrganizationcode());
//                    consignmentBillMaster.setConsignmentbillmasterid(s.getConsignmentbillmasterid());
//                    consignmentBillMaster.setDatatype(s.getDatatype());
//                    consignmentBillMaster.setSystime(s.getSystime());
                    //listByConsignmentBillMasterDelete.add(consignmentBillMaster);
                }else{
                    log.error("订单查询异常Datatype error id:"+s.getConsignmentbillmasterid());
                }
            } catch (Exception e) {
                log.info(insertSql + " values " + "(" + s.getSqltext() + ")" + "error9:", e);
                e.printStackTrace();
            }

        }

        end = System.currentTimeMillis();
        try {
            //不应该注释
            //String f6sql="delete from GoodsTracked_Record_Top where  sysTime<='" + datetime + "'";
            //f6sql=" update GoodsTracked_Record_Top set datatype='100' where  sysTime<='" + datetime + "' and (datatype!='100' or datatype is null)";
            if(!ConsignmentBillMasterIdStr.isEmpty()){
                String f6sql =" update GoodsTracked_Record_Top set datatype='100' where ConsignmentBillMasterId in(" + ConsignmentBillMasterIdStr + ")";
                log.info("更新语句:" + f6sql);
                jdbcDao.updateForSql(f6sql);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        log.info("DataSource:" + dataSource + ";Databasename:" + databasename + ";保存货运单数据消耗" + (end - begin) + "豪秒。");

        //货物跟踪数据
        begin = System.currentTimeMillis();
        insertSql = "insert into GoodsTracked_Record_Public(OrganizationCode, ConsignmentBillMasterId,TypeNum,TypeName, Info, SysTime,CreateOperator, CreateCompanyName,DataType,OtherNumber" +
                ",DriverCode,DriverName,CarLicense,Longitude,Latitude)";
        seleclSql = "select ''''+ OrganizationCode+''','''+ConsignmentBillMasterId+''','+convert(varchar,(case when TypeNum='' then 99 else typenum end))+','''+TypeName+''','''+Info+''','''+ CONVERT(varchar(100), SysTime, 20)+''','''+CreateOperator+''','''+CreateCompanyName+''','+convert(varchar(20),convert(decimal(18,0),DataType))+','''+OtherNumber+'''" +
                ",'''+ DriverCode+'''" +
                ",'''+ DriverName+'''" +
                ",'''+ CarLicense+'''" +
                ",'''+ cast(Longitude as varchar)+'''" +
                ",'''+  cast(Latitude as varchar)+'''" +
                " ' sqltext,CONVERT(varchar(100), SysTime, 20) systime,convert(varchar(20),convert(decimal(18,0),DataType)) datatype,organizationcode,consignmentbillmasterid,typenum " +
                " from GoodsTracked_Record"

                + "  where SysTime<='" + datetime + "'";
        List<GoodsTrack> list=null;
        try{
            list = jdbcDao.queryListForSql(seleclSql, GoodsTrack.class);
        }catch (Exception e){
            log.error("DataSource:" + dataSource + ";Databasename:" + databasename +"货物跟踪 error",e);
        }

        end = System.currentTimeMillis();
        log.info("DataSource:" + dataSource + ";Databasename:" + databasename + ";货物跟踪数据：" + list.size() + ";消耗" + (end - begin) + "豪秒。");
        //log.info("货物跟踪查询sql："+seleclSql);
        GoodsTrack goodsTrack = new GoodsTrack();
        begin = System.currentTimeMillis();
        sqlArray0 = "";
        for (GoodsTrack s : list) {
            try {
                if (s.getDatatype().equals("0") || s.getDatatype().equals("1")) {
                    try {
                        jdbcDao_GoodsTracked.updateForSql(insertSql + " values (" + s.getSqltext() + ")");
                    } catch (Exception e) {
                        e.printStackTrace();
                    }

                    if (s.getDatatype().equals("1")) {
                        goodsTrack = new GoodsTrack();
                        goodsTrack.setOrganizationcode(s.getOrganizationcode());
                        goodsTrack.setConsignmentbillmasterid(s.getConsignmentbillmasterid());
                        goodsTrack.setTypenum(s.getTypenum());
                        goodsTrack.setDatatype(s.getDatatype());
                        goodsTrack.setSystime(s.getSystime());
                        listByDelete.add(goodsTrack);
                    }

                } else if (s.getDatatype().equals("2")) {
                    goodsTrack.setOrganizationcode(s.getOrganizationcode());
                    goodsTrack.setConsignmentbillmasterid(s.getConsignmentbillmasterid());
                    goodsTrack.setDatatype(s.getDatatype());
                    goodsTrack.setTypenum(s.getTypenum());
                    goodsTrack.setSystime(s.getSystime());
                    listByDelete.add(goodsTrack);
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
/*        if (sqlArray0.length() > 10) {
            jdbcDao_GoodsTracked.updateForSql(insertSql + " values " + sqlArray0.substring(1, sqlArray0.length()));
        }*/
        //处理修改、删除记录
        try {
            for (GoodsTrack s : listByDelete) {
                if(s.getTypenum()==null||s.getTypenum().length()==0){
                    jdbcDao_GoodsTracked.updateForSql("delete from GoodsTracked_Record_Public where " +
                            "OrganizationCode='" + s.getOrganizationcode() + "'"
                            + " and OtherNumber='" + s.getConsignmentbillmasterid() + "'"
                            + " and TypeNum is null"
                            + " and sysTime<'" + s.getSystime() + "'");
                }else{
                    jdbcDao_GoodsTracked.updateForSql("delete from GoodsTracked_Record_Public where " +
                            "OrganizationCode='" + s.getOrganizationcode() + "'"
                            + " and OtherNumber='" + s.getConsignmentbillmasterid() + "'"
                            + " and TypeNum=" + s.getTypenum()
                            + " and sysTime<'" + s.getSystime() + "'");
                }

            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        end = System.currentTimeMillis();
        try {
            //不应该注释
            jdbcDao.updateForSql("delete from GoodsTracked_Record where  sysTime<='" + datetime + "'");
            log.info("delete from GoodsTracked_Record where  sysTime<='" + datetime + "'");
        } catch (Exception e) {
            log.error("删除失败:" + e.getMessage());
            e.printStackTrace();
        }
        log.info("DataSource:" + dataSource + ";Databasename:" + databasename + ";保存货物跟踪数据消耗" + (end - begin) + "豪秒。");
    }


    public List<GoodstrackedSetingPublic> list() {
        return goodsTrackedSetingPublicService.list();
    }

    @Override
    public void insert_car(String dataSource, String databasename, String wxpublicnumber, String appid) {
        if (wxpublicnumber == null) {
            wxpublicnumber = "";
        }
        if (appid == null) {
            appid = "";
        }

        String datetime = DateUtil.Now();

        int numTmp = 0;
        String sqlArray0 = "";
        int i = 1;

        List<GoodsTrack> listByDelete = new ArrayList<GoodsTrack>();
        Jrpt jrpt;
        String upload;


        Long begin;
        String insertSql;
        String seleclSql;
        Long end;

        //同步派车记录
        ConsignmentBillMaster consignmentBillMaster = new ConsignmentBillMaster();
        List<ConsignmentBillMaster> listByConsignmentBillMasterDelete = new ArrayList<ConsignmentBillMaster>();

        int zs=0;
        try{
            String countSql= " select count(*) count " +
                    "  from TransportBillMaster_Temp t WITH(NOLOCK) " +
                    "  left join Sys_Company c on t.OrganizationCode=c.OrganizationCode AND t.sendCompany=c.CompanyName "+
                    "  left join Sys_Company d on t.OrganizationCode=d.OrganizationCode AND t.ReceiveCompany=d.CompanyName "+
                    "  where t.SysTime<='" + datetime + "'";
            Map<String, Object> countObj =jdbcDao.querySingleResultForSql(countSql);
            if(countObj != null && countObj.get("count") != null){
                zs= Integer.valueOf(countObj.get("count").toString());
            }else if(countObj != null && countObj.get("COUNT") != null){
                zs= Integer.valueOf(countObj.get("COUNT").toString());
            }
        }catch (Exception e){

        }


        int max=10000;
        if(zs>max){
            int num = zs / max;
            num = zs % max != 0 ? num + 1 : num;
            for (int j = 0; j < num; j++) {
                begin = System.currentTimeMillis();
                seleclSql = " select top "+(j*max)+" TransportBillMasterId, ISNULL( d.CompanyName, t.sendCompany) ReceiveCompany , t.*,c.FyCloudCode tenant,d.FyCloudCode endCompanyCode,c.FyCloudPassword tenantPass " +
                        "  from TransportBillMaster_Temp t WITH(NOLOCK) " +
                        "  left join Sys_Company c on t.OrganizationCode=c.OrganizationCode AND t.sendCompany=c.CompanyName "+
                        "  left join Sys_Company d on t.OrganizationCode=d.OrganizationCode AND t.ReceiveCompany=d.CompanyName "+
                        "  where t.SysTime<='" + datetime + "'";
                // log.info(seleclSql);
                List<TransportbillmasterPublic> listByConsignmentBillMaster = jdbcDao.queryListForSql(seleclSql, TransportbillmasterPublic.class);
                end = System.currentTimeMillis();
                log.info("DataSource:" + dataSource + ";Databasename:" + databasename + ";派车单数据：" + listByConsignmentBillMaster.size() + ";消耗" + (end - begin) + "豪秒。");
                numTmp = 0;
                sqlArray0 = "";
                i = 1;
                List<TransportbillmasterPublic> deleteList=new ArrayList<TransportbillmasterPublic>();
                List<TransportbillmasterPublic> updateList=new ArrayList<TransportbillmasterPublic>();
                begin = System.currentTimeMillis();
                for (TransportbillmasterPublic s : listByConsignmentBillMaster) {
                    try {
                        try {
                            s = (TransportbillmasterPublic)killNull(s);
                            if (s.getDatatype()==0){
                                jdbcDao_GoodsTracked.insert(s);
                            }else if(s.getDatatype()==2){
                                deleteList.add(s);
                                jdbcDao_GoodsTracked.insert(s);
                            }else if(s.getDatatype()==1){
                                updateList.add(s);
                                jdbcDao_GoodsTracked.insert(s);
                            }
                        } catch (Exception e) {
                            log.info("jdbcDao_GoodsTracked", e);
                            e.printStackTrace();
                        }

                    } catch (Exception e) {
                        log.info("jdbcDao_GoodsTracked.insert", e);
                        e.printStackTrace();
                    }
                }
                end = System.currentTimeMillis();
                try {
                    //不应该注释
                    jdbcDao.updateForSql("delete from TransportBillMaster_Temp where " +
                            " where TransportBillMasterId in(select top "+(j*max)+" TransportBillMasterId from  TransportBillMaster_Temp sysTime<='" + datetime + "')");
                } catch (Exception e) {
                    e.printStackTrace();
                }
                log.info("DataSource:" + dataSource + ";Databasename:" + databasename + ";保存paich数据消耗" + (end - begin) + "豪秒。");
            }
        }else{
            begin = System.currentTimeMillis();
            seleclSql = " select ISNULL( d.CompanyName, t.sendCompany) ReceiveCompany , t.*,c.FyCloudCode tenant,d.FyCloudCode endCompanyCode,c.FyCloudPassword tenantPass " +
                    "  from TransportBillMaster_Temp t WITH(NOLOCK) " +
                    "  left join Sys_Company c on t.OrganizationCode=c.OrganizationCode AND t.sendCompany=c.CompanyName "+
                    "  left join Sys_Company d on t.OrganizationCode=d.OrganizationCode AND t.ReceiveCompany=d.CompanyName "+
                    "  where t.SysTime<='" + datetime + "'";
            // log.info(seleclSql);
            List<TransportbillmasterPublic> listByConsignmentBillMaster = jdbcDao.queryListForSql(seleclSql, TransportbillmasterPublic.class);
            end = System.currentTimeMillis();
            log.info("DataSource:" + dataSource + ";Databasename:" + databasename + ";派车单数据：" + listByConsignmentBillMaster.size() + ";消耗" + (end - begin) + "豪秒。");
            numTmp = 0;
            sqlArray0 = "";
            i = 1;
            List<TransportbillmasterPublic> deleteList=new ArrayList<TransportbillmasterPublic>();
            List<TransportbillmasterPublic> updateList=new ArrayList<TransportbillmasterPublic>();
            begin = System.currentTimeMillis();
            for (TransportbillmasterPublic s : listByConsignmentBillMaster) {
                try {
                    try {
                        s = (TransportbillmasterPublic)killNull(s);
                        if (s.getDatatype()==0){
                            jdbcDao_GoodsTracked.insert(s);
                        }else if(s.getDatatype()==2){
                            deleteList.add(s);
                            jdbcDao_GoodsTracked.insert(s);
                        }else if(s.getDatatype()==1){
                            updateList.add(s);
                            jdbcDao_GoodsTracked.insert(s);
                        }
                    } catch (Exception e) {
                        log.info("jdbcDao_GoodsTracked", e);
                        e.printStackTrace();
                    }

                } catch (Exception e) {
                    log.info("jdbcDao_GoodsTracked.insert", e);
                    e.printStackTrace();
                }
            }
            end = System.currentTimeMillis();
            try {
                //不应该注释
                jdbcDao.updateForSql("delete from TransportBillMaster_Temp where  sysTime<='" + datetime + "'");
            } catch (Exception e) {
                e.printStackTrace();
            }
            log.info("DataSource:" + dataSource + ";Databasename:" + databasename + ";保存paich数据消耗" + (end - begin) + "豪秒。");


        }


    }
    @Override
    public void insert_CollectingMoney(String dataSource, String databasename, String wxpublicnumber, String appid) {
        if (wxpublicnumber == null) {
            wxpublicnumber = "";
        }
        if (appid == null) {
            appid = "";
        }
        String datetime = DateUtil.Now();
        int numTmp = 0;
        String sqlArray0 = "";
        int i = 1;
        List<GoodsTrack> listByDelete = new ArrayList<GoodsTrack>();
        Jrpt jrpt;
        String upload;
        Long begin;
        String insertSql;
        String seleclSql;
        Long end;
        //同步代收记录
        ConsignmentBillMaster consignmentBillMaster = new ConsignmentBillMaster();
        int zs=0;
        try{
            String countSql= " select count(*) as count  from CollectionGoodsValueAccount_Temp t WITH(NOLOCK)  " +
                    "  left join Sys_Company c on t.OrganizationCode=c.OrganizationCode AND t.sendCompany=c.CompanyName "+
                    "  where t.SysTime<='" + datetime + "'";
            Map<String, Object> countObj =jdbcDao.querySingleResultForSql(countSql);
            if(countObj != null && countObj.get("count") != null){
                zs= Integer.valueOf(countObj.get("count").toString());
            }else if(countObj != null && countObj.get("COUNT") != null){
                zs= Integer.valueOf(countObj.get("COUNT").toString());
            }
        }catch (Exception e){

        }

        int max=10000;
        if(zs>max){
            int num = zs / max;
            num = zs % max != 0 ? num + 1 : num;
            for (int j = 0; j < num; j++) {
                //查询每一百条记录  j * max, (j + 1) * max
                begin = System.currentTimeMillis();
                seleclSql = " select TOP  "+(j * max)+"  CollectionGoodsValueAccountId, t.*,c.FyCloudCode tenant,c.FyCloudPassword tenantPass ,(CASE WHEN GiveDate IS NULL THEN '未发放' WHEN GiveType='现金' THEN '已发放'  WHEN FileExportDate IS NULL THEN '已发放' WHEN TransferAccountResult='成功' THEN '转账成功' WHEN TransferAccountResult='失败' THEN '转账失败' ELSE '已导出' END) AS DataStatus ," +
                        "(case when (IsNull(CollectionGoodsValue,0)-IsNull(CollectionGoodsValueDec,0) - IsNull(DeductionTransportCost,0)- IsNull(CollectionGoodsValueInc,0))<0 then 0 else (IsNull(CollectionGoodsValue,0)-IsNull(CollectionGoodsValueDec,0) - IsNull(DeductionTransportCost,0)- IsNull(CollectionGoodsValueInc,0)) End -IsNull(SettlementInsuranceCost,0)-IsNull(ExtraCost,0)-IsNull(ExceedExtraCost,0)-IsNull(LoseExtraCost,0)-IsNull(ShortMessageCharge,0)-IsNull(ExtraCostOverCharge,0)+IsNull(ExtraCostDecreaseCharge,0)) paymentmoney "+
                        "  from CollectionGoodsValueAccount_Temp t WITH(NOLOCK)  " +
                        "  left join Sys_Company c on t.OrganizationCode=c.OrganizationCode AND t.sendCompany=c.CompanyName "+
                        "  where t.SysTime<='" + datetime + "' order by t.SysTime";
                List<CollectiongoodsvalueaccountPublic> listByConsignmentBillMaster = jdbcDao.queryListForSql(seleclSql, CollectiongoodsvalueaccountPublic.class);
                end = System.currentTimeMillis();
                log.info("DataSource:" + dataSource + ";Databasename:" + databasename + ";代收数据：" + listByConsignmentBillMaster.size() + ";消耗" + (end - begin) + "豪秒。");
                numTmp = 0;
                sqlArray0 = "";
                i = 1;
                begin = System.currentTimeMillis();
                for (CollectiongoodsvalueaccountPublic s : listByConsignmentBillMaster) {
                    try {
                        try {
                            s = (CollectiongoodsvalueaccountPublic)killNull(s);
                            if (s.getDatatype()==0){
                                jdbcDao_GoodsTracked.insert(s);
                            }else if(s.getDatatype()==1){
                                String sql ="update CollectionGoodsValueAccount_Public " +
                                        " set flag='',dataType='1',dataStatus='"+ s.getDatastatus() +"'  where CollectionGoodsValueAccountId='"+s.getCollectiongoodsvalueaccountid()+"'";
                                jdbcDao_GoodsTracked.updateForSql(sql);
                            }else if(s.getDatatype()==2){
                                String sql ="update CollectionGoodsValueAccount_Public " +
                                        " set flag='',dataType='2' where CollectionGoodsValueAccountId='"+s.getCollectiongoodsvalueaccountid()+"'";
                                jdbcDao_GoodsTracked.updateForSql(sql);
                            }
                        } catch (Exception e) {
                            log.info("insert_CollectingMoney", e);
                            e.printStackTrace();
                        }

                    } catch (Exception e) {
                        log.info("insert_CollectingMoney.insert", e);
                        e.printStackTrace();
                    }
                }
                try {
                    //不应该注释
                    jdbcDao.updateForSql("delete from CollectionGoodsValueAccount_Temp where " +
                            " where  CollectionGoodsValueAccountId in(select top "+(j * max)+" CollectionGoodsValueAccountId from CollectionGoodsValueAccount_Temp " +
                            " sysTime<='" + datetime + "')");
                } catch (Exception e) {
                    e.printStackTrace();
                }
                end = System.currentTimeMillis();
                log.info("DataSource:" + dataSource + ";Databasename:" + databasename + ";保存代收数据消耗" + (end - begin) + "豪秒。");
            }
        }else{
            begin = System.currentTimeMillis();
            seleclSql = " select t.*,c.FyCloudCode tenant,c.FyCloudPassword tenantPass ,(CASE WHEN GiveDate IS NULL THEN '未发放' WHEN GiveType='现金' THEN '已发放'  WHEN FileExportDate IS NULL THEN '已发放' WHEN TransferAccountResult='成功' THEN '转账成功' WHEN TransferAccountResult='失败' THEN '转账失败' ELSE '已导出' END) AS DataStatus ," +
                    "(case when (IsNull(CollectionGoodsValue,0)-IsNull(CollectionGoodsValueDec,0) - IsNull(DeductionTransportCost,0)- IsNull(CollectionGoodsValueInc,0))<0 then 0 else (IsNull(CollectionGoodsValue,0)-IsNull(CollectionGoodsValueDec,0) - IsNull(DeductionTransportCost,0)- IsNull(CollectionGoodsValueInc,0)) End -IsNull(SettlementInsuranceCost,0)-IsNull(ExtraCost,0)-IsNull(ExceedExtraCost,0)-IsNull(LoseExtraCost,0)-IsNull(ShortMessageCharge,0)-IsNull(ExtraCostOverCharge,0)+IsNull(ExtraCostDecreaseCharge,0)) paymentmoney "+
                    "  from CollectionGoodsValueAccount_Temp t WITH(NOLOCK)  " +
                    "  left join Sys_Company c on t.OrganizationCode=c.OrganizationCode AND t.sendCompany=c.CompanyName "+
                    "  where t.SysTime<='" + datetime + "' order by t.SysTime";
            List<CollectiongoodsvalueaccountPublic> listByConsignmentBillMaster = jdbcDao.queryListForSql(seleclSql, CollectiongoodsvalueaccountPublic.class);
            end = System.currentTimeMillis();
            log.info("DataSource:" + dataSource + ";Databasename:" + databasename + ";代收数据：" + listByConsignmentBillMaster.size() + ";消耗" + (end - begin) + "豪秒。");
            numTmp = 0;
            sqlArray0 = "";
            i = 1;
            begin = System.currentTimeMillis();
            for (CollectiongoodsvalueaccountPublic s : listByConsignmentBillMaster) {
                try {
                    try {
                        s = (CollectiongoodsvalueaccountPublic)killNull(s);
                        if (s.getDatatype()==0){
                            jdbcDao_GoodsTracked.insert(s);
                        }else if(s.getDatatype()==1){
                            String sql ="update CollectionGoodsValueAccount_Public " +
                                    " set flag='',dataType='1',dataStatus='"+ s.getDatastatus() +"'  where CollectionGoodsValueAccountId='"+s.getCollectiongoodsvalueaccountid()+"'";
                            jdbcDao_GoodsTracked.updateForSql(sql);
                        }else if(s.getDatatype()==2){
                            String sql ="update CollectionGoodsValueAccount_Public " +
                                    " set flag='',dataType='2' where CollectionGoodsValueAccountId='"+s.getCollectiongoodsvalueaccountid()+"'";
                            jdbcDao_GoodsTracked.updateForSql(sql);
                        }
                    } catch (Exception e) {
                        log.info("insert_CollectingMoney", e);
                        e.printStackTrace();
                    }

                } catch (Exception e) {
                    log.info("insert_CollectingMoney.insert", e);
                    e.printStackTrace();
                }
            }
            end = System.currentTimeMillis();
            log.info("DataSource:" + dataSource + ";Databasename:" + databasename + ";保存代收数据消耗" + (end - begin) + "豪秒。");
            try {
                //不应该注释
                jdbcDao.updateForSql("delete from CollectionGoodsValueAccount_Temp where  sysTime<='" + datetime + "'");
            } catch (Exception e) {
                e.printStackTrace();
            }
        }



    }
    public static Object killNull(Object object) throws Exception {
        //遍历object类 成员为String类型 属性为空的全部替换为""
        Field[] fields = object.getClass().getDeclaredFields();

        for (int i = 0; i < fields.length; i++) {
            // 获取属性的名字
            String name = fields[i].getName();
            // 将属性的首字符大写，方便构造get，set方法
            name = name.substring(0, 1).toUpperCase() + name.substring(1);
            // 获取属性的类型
            String type = fields[i].getGenericType().toString();
//            System.out.println(name+" "+type);
            // 如果type是类类型，则前面包含"class "，后面跟类名
            if (type.equals("class java.lang.String")) {
                Method m = object.getClass().getMethod("get" + name);
                // 调用getter方法获取属性值
                String value = (String) m.invoke(object);
                if (value == null) {
                    Class[] parameterTypes = new Class[1];
                    parameterTypes[0] = fields[i].getType();
                    m = object.getClass().getMethod("set" + name, parameterTypes);
                    String string = new String("");
                    Object[] objects = new Object[1];
                    objects[0] = string;
                    m.invoke(object, objects);
                }
            }else if(type.equals("class java.math.BigDecimal")){
                Method m = object.getClass().getMethod("get" + name);
                // 调用getter方法获取属性值
                BigDecimal value = (BigDecimal) m.invoke(object);
                if (value == null) {
                    Class[] parameterTypes = new Class[1];
                    parameterTypes[0] = fields[i].getType();
                    m = object.getClass().getMethod("set" + name, parameterTypes);
                    BigDecimal bigDecimal = new BigDecimal(0);
                    Object[] objects = new Object[1];
                    objects[0] = bigDecimal;
                    m.invoke(object, objects);
                }
            }else if(type.equals(" class java.lang.Integer")){
                Method m = object.getClass().getMethod("get" + name);
                // 调用getter方法获取属性值
                Integer value = (Integer) m.invoke(object);
                if (value == null) {
                    Class[] parameterTypes = new Class[1];
                    parameterTypes[0] = fields[i].getType();
                    m = object.getClass().getMethod("set" + name, parameterTypes);
                    Integer integer = new Integer(0);
                    Object[] objects = new Object[1];
                    objects[0] = integer;
                    m.invoke(object, objects);
                }
            }
            //将形参object类中在com.yu.killnull包下的引用一块遍历修改
            if (type.contains("class com.yu.killnull")) {
                Method m = object.getClass().getMethod("get" + name);
                // 调用getter方法获取属性值
                Object value = m.invoke(object);
                killNull(value);
            }
        }
        return object;

    }
    @Override
    public void insert_car_buchuan(String dataSource, String databasename, String wxpublicnumber, String appid
                                   ,String CreateTimeBegin,String CreateTimeEnd,String OrganizationCode) {
        if (wxpublicnumber == null) {
            wxpublicnumber = "";
        }
        if (appid == null) {
            appid = "";
        }
        String sqlArray0 = "";
        List<GoodsTrack> listByDelete = new ArrayList<GoodsTrack>();
        Long begin;
        String seleclSql;
        Long end;
        begin = System.currentTimeMillis();
//        seleclSql = " select  ISNULL( d.CompanyName, t.sendCompany) ReceiveCompany , t.*,c.FyCloudCode tenant,d.FyCloudCode endCompanyCode,c.FyCloudPassword tenantPass " +
//                "  from TransportBillMaster_Temp t WITH(NOLOCK) " +
//                "  left join Sys_Company c on t.OrganizationCode=c.OrganizationCode AND t.sendCompany=c.CompanyName "+
//                "  left join Sys_Company d on t.OrganizationCode=d.OrganizationCode AND t.ReceiveCompany=d.CompanyName "+
//                "  where t.SysTime<='" + datetime + "'";

        try{
            List<Jrpt> jrpts = jrptService.select();
            for (Jrpt jrpt : jrpts) {
                if(jrpt!=null&&jrpt.getOrganizationcode()!=null&&jrpt.getOrganizationcode().length()!=0){
                    OrganizationCode=jrpt.getOrganizationcode();
                    seleclSql=" select ISNULL( c.CompanyName, b.CompanyName) ReceiveCompany,b.FyCloudCode tenant, ISNULL(c.FyCloudCode, b.FyCloudCode) endCompanyCode,b.FyCloudPassword tenantPass ,a.OrganizationCode,a.TransportBillMasterId,TransportBillNumber,TransportBillType,TransportStatus    " +
                            "      ,TransportMode,TransportStartDate,PredictArriveDate,ConfirmOperator,ConfirmDate,ArriveDate   " +
                            "      ,ReceiveOperator,ReceiveDate,TransportBeginPlace,SendCompany,TransportEndPlace  " +
                            "      ,CarNumber,TrailerNumber,DriverName,CopilotName,DriverTelephone,CopilotTelephone,OverseePersonnel   " +
                            "      ,CheckPersonnel,TransportBillRemark,ContractNumber,TotalFare,AlreadyFare,ArriveFare,ReturnFare   " +
                            "      ,UnloadCost,ManualCost,ManageGoodsCost,TransferCost,DriverCollectionCost,DriverAdvanceCost,RunLicenseNo,CarFrameNumber  " +
                            "      ,DriverLicenseNumber,CarModelNumber,EngineNumber,Carrier,YBackupStringA,YBackupStringB,YBackupStringC,YBackupStringD,YBackupStringE,YBackupStringF " +
                            "      ,YBackupStringG,YBackupStringH,YBackupMoneyA,YBackupMoneyB,YBackupMoneyC,YBackupMoneyD,YBackupMoneyE,YBackupMoneyF " +
                            "      ,YBackupMoneyG,YBackupMoneyH,YBackupDateA,YBackupDateB,YBackupDateC,YBackupDateD,DailyAccountList, " +
                            "      0 AS DataType,GETDATE() SysTime," +
                            " convert(decimal(18,0),ISNULL(b.Longitude,'0')) TransportBeginLongitude," +
                            " convert(decimal(18,0),ISNULL(b.Latitude,'0')) TransportBeginLatitude," +
                            "  convert(decimal(18,0),ISNULL(c.Longitude,'0')) TransportEndLongitude, " +
                            "    convert(decimal(18,0),ISNULL(c.Latitude,'0'))   TransportEndLatitude,ConsignmentBillCount,TotalNumberOfPackages,TotalWeight,TotalVolume,TotalTransportCost,CollectionGoodsValue  " +
                            "      from TransportBillMaster as a WITH(NOLOCK) left join (select TransportBillMasterId,count(TransportBillMasterId) as ConsignmentBillCount " +
                            "     ,sum(TotalNumberOfPackages) as TotalNumberOfPackages,sum(TotalWeight) as TotalWeight,sum(TotalVolume) as TotalVolume,sum(TotalTransportCost) as TotalTransportCost " +
                            "     ,sum(CollectionGoodsValue) as CollectionGoodsValue from TransportBillDetails aa WITH(NOLOCK) inner join ConsignmentBillMaster bb WITH(NOLOCK) on aa.ConsignmentBillMasterId=bb.ConsignmentBillMasterId " +
                            "      group by TransportBillMasterId) trans on a.TransportBillMasterId=trans.TransportBillMasterId  " +
                            "      left join(select * from Sys_Company as b WITH(NOLOCK) where OrganizationCode='"+OrganizationCode+"' ) b on a.TransportBeginPlace=b.CompanyName " +
                            "      left join (select * from Sys_Company as c WITH(NOLOCK) where OrganizationCode='"+OrganizationCode+"') c on a.TransportEndPlace=c.CompanyName " +
                            "      where  a.TransportStartDate>='"+CreateTimeBegin+"'" +
                            " AND a.TransportStartDate<='"+CreateTimeEnd+"' AND a.OrganizationCode='"+OrganizationCode+"'"+
                            "      UNION  " +
                            "       select ISNULL( c.CompanyName, b.CompanyName) ReceiveCompany,b.FyCloudCode tenant, ISNULL(c.FyCloudCode, b.FyCloudCode)  endCompanyCode,b.FyCloudPassword tenantPass ,a.OrganizationCode,a.TransportBillMasterId,TransportBillNumber,TransportBillType,TransportStatus    " +
                            "      ,TransportMode,TransportStartDate,PredictArriveDate,ConfirmOperator,ConfirmDate,ArriveDate   " +
                            "      ,ReceiveOperator,ReceiveDate,TransportBeginPlace,SendCompany,TransportEndPlace  " +
                            "      ,CarNumber,TrailerNumber,DriverName,CopilotName,DriverTelephone,CopilotTelephone,OverseePersonnel   " +
                            "      ,CheckPersonnel,TransportBillRemark,ContractNumber,TotalFare,AlreadyFare,ArriveFare,ReturnFare  \n" +
                            "      ,UnloadCost,ManualCost,ManageGoodsCost,TransferCost,DriverCollectionCost,DriverAdvanceCost,RunLicenseNo,CarFrameNumber \n" +
                            "      ,DriverLicenseNumber,CarModelNumber,EngineNumber,Carrier,YBackupStringA,YBackupStringB,YBackupStringC,YBackupStringD,YBackupStringE,YBackupStringF \n" +
                            "      ,YBackupStringG,YBackupStringH,YBackupMoneyA,YBackupMoneyB,YBackupMoneyC,YBackupMoneyD,YBackupMoneyE,YBackupMoneyF \n" +
                            "      ,YBackupMoneyG,YBackupMoneyH,YBackupDateA,YBackupDateB,YBackupDateC,YBackupDateD,DailyAccountList,\n" +
                            "      0,GETDATE()," +
                            "  convert(decimal(18,0),ISNULL(b.Longitude,'0')) ," +
                            " convert(decimal(18,0),ISNULL(b.Latitude,'0'))," +
                            " convert(decimal(18,0),ISNULL(c.Longitude,'0'))," +
                            "  convert(decimal(18,0),ISNULL(c.Latitude,'0'))," +
                            "ConsignmentBillCount,TotalNumberOfPackages,TotalWeight,TotalVolume,TotalTransportCost,CollectionGoodsValue \n" +
                            "      from OrderTransportMaster as a WITH(NOLOCK) left join (select TransportBillMasterId,count(TransportBillMasterId) as ConsignmentBillCount\n" +
                            "     ,sum(TotalNumberOfPackages) as TotalNumberOfPackages,sum(TotalWeight) as TotalWeight,sum(TotalVolume) as TotalVolume,sum(TotalTransportCost) as TotalTransportCost\n" +
                            "     ,sum(CollectionGoodsValue) as CollectionGoodsValue from OrderTransportDetails aa WITH(NOLOCK) inner join OrderBillMaster bb WITH(NOLOCK) on aa.OrderBillMasterId=bb.OrderBillMasterId\n" +
                            "      group by TransportBillMasterId) trans on a.TransportBillMasterId=trans.TransportBillMasterId \n" +
                            "      left join(select * from Sys_Company as b WITH(NOLOCK) where OrganizationCode='"+OrganizationCode+"' ) b on a.TransportBeginPlace=b.CompanyName\n" +
                            "      left join (select * from Sys_Company as c WITH(NOLOCK) where OrganizationCode='"+OrganizationCode+"') c on a.TransportEndPlace=c.CompanyName\n" +
                            "      where a.TransportStartDate>='"+CreateTimeBegin+"'" +
                            " AND a.TransportStartDate<='"+CreateTimeEnd+"' AND a.OrganizationCode='"+OrganizationCode+"'";
//                    log.info("pcmb sql:"+seleclSql);
                    List<TransportbillmasterPublic> listByConsignmentBillMaster = jdbcDao.queryListForSql(seleclSql, TransportbillmasterPublic.class);
                    String numSql="";
                    int num=0;

                    for (TransportbillmasterPublic car : listByConsignmentBillMaster) {
                        try {
                            ResultModel reModel=jrptExecutor.uploadCar(SystemConstants.CAR_URL,car);
                            if (reModel!=null&&reModel.getCode()==100){
                                num=num+1;
                            }else {
//                                car = (TransportbillmasterPublic)killNull(car);
                                car.setTrackingno("补传失败"+reModel.getMessage());
                                jdbcDao_GoodsTracked.insert(car);
                            }
                        }catch (Exception e){
                            log.error("补传异常"+JSONObject.toJSONString(car),e);
                        }

                    }
                    try{
                        numSql="insert into bigdata(type,num,uploaddate,tenant) values ('car',"+num+",'"+CreateTimeBegin+"','"+OrganizationCode+"')" ;
                        jdbcDao_GoodsTracked.updateForSql(numSql);
                    }catch (Exception e){
                        log.error(" bigdata car error",e);
                    }
                    end = System.currentTimeMillis();
                    log.info("DataSource:" + dataSource + ";Databasename:" + databasename + ";派车单数据：" + listByConsignmentBillMaster.size() + ";消耗" + (end - begin) + "豪秒。");
                }
            }
        }catch (Exception e){

            log.error("pcmb error:",e);
        }finally {

        }
         }

    @Override
    public void insert_dshk_buchuan(String dataSource, String databasename, String wxpublicnumber, String appid
            ,String CreateTimeBegin,String CreateTimeEnd,String OrganizationCode) {
        if (wxpublicnumber == null) {
            wxpublicnumber = "";
        }
        if (appid == null) {
            appid = "";
        }
        String sqlArray0 = "";
        List<GoodsTrack> listByDelete = new ArrayList<GoodsTrack>();
        Long begin;
        String seleclSql;
        Long end;
        begin = System.currentTimeMillis();
//        seleclSql = " select TOP  "+(j * max)+"  CollectionGoodsValueAccountId, t.*,c.FyCloudCode tenant,c.FyCloudPassword tenantPass ," +
//                "(case when (IsNull(CollectionGoodsValue,0)-IsNull(CollectionGoodsValueDec,0) - IsNull(DeductionTransportCost,0)- IsNull(CollectionGoodsValueInc,0))<0 then 0 else (IsNull(CollectionGoodsValue,0)-IsNull(CollectionGoodsValueDec,0) - IsNull(DeductionTransportCost,0)- IsNull(CollectionGoodsValueInc,0)) End -IsNull(SettlementInsuranceCost,0)-IsNull(ExtraCost,0)-IsNull(ExceedExtraCost,0)-IsNull(LoseExtraCost,0)-IsNull(ShortMessageCharge,0)-IsNull(ExtraCostOverCharge,0)+IsNull(ExtraCostDecreaseCharge,0)) paymentmoney "+
//                "  from CollectionGoodsValueAccount_Temp t WITH(NOLOCK)  " +
//                "  left join Sys_Company c on t.OrganizationCode=c.OrganizationCode AND t.sendCompany=c.CompanyName "+
//                "  where t.SysTime<='" + datetime + "'";
//        List<CollectiongoodsvalueaccountPublic> listByConsignmentBillMaster = jdbcDao.queryListForSql(seleclSql, CollectiongoodsvalueaccountPublic.class);

        try{
            List<Jrpt> jrpts = jrptService.select();
            for (Jrpt jrpt : jrpts) {
                if(jrpt!=null&&jrpt.getOrganizationcode()!=null&&jrpt.getOrganizationcode().length()!=0){
                    OrganizationCode=jrpt.getOrganizationcode();
                    seleclSql=" select c.FyCloudCode tenant,c.FyCloudPassword tenantPass,a.OrganizationCode OrganizationCode,CollectionGoodsValueAccountId,a.ConsignmentBillMasterId,ConsignmentBillNumber,TransportStartDate \n" +
                            "      ,CollectionGoodsValue,CollectionGoodsValueDec,DeductionTransportCost,ReturnMoneyId \n" +
                            "      ,ReturnMoneyNumber,ReturnMoneyFlag,ReturnMoneyDate,ReturnMoneyCompanyName,ReturnMoneyOperator,GiveFlag \n" +
                            "      ,GiveDate,GiveCompanyName,GiveOperator,GiveRemark,ExtraCost,ExceedExtraCost,LoseExtraCost,ExtraCostOverCharge,ExtraCostDecreaseCharge \n" +
                            "      ,ShortMessageCharge,AdvancedExtraCost,YearlyPayment,YearlyPaymentCollection,OneCardId,OneCardCode,BankName \n" +
                            "      ,AccountHolder,Account,AccountType,ShortMessageMobileTelephone,DoPerson,IdCard,SingleNumber,ExtraCostCountFlag \n" +
                            "      ,BatchNumber,FileExportFlag,FileExportDate,FileExportOperator,FileExportCompanyName,TransferAccountDate,TransferAccountOperator \n" +
                            "      ,TransferAccountCompanyName,TransferAccountResult,TransferAccountRemark,GiveType,CheckOperator,CheckDate,CheckRemark  \n" +
                            "      ,CheckCompanyName,RecheckOperator,RecheckDate,RecheckRemark,RecheckCompanyName,TempNotPaymentOperator \n" +
                            "      ,TempNotPaymentTime,TempNotPaymentRemark,CanPaymentOperator,CanPaymentTime,CanPaymentRemark,CollectionGoodsValueInc \n" +
                            "      ,CollectionGoodsValueDate,CollectionGoodsValueRemark,CollectionGoodsValueCompany,CollectionGoodsValueOperator \n" +
                            "      ,SettlementInsuranceCost,0 DataType,GETDATE() SysTime\n" +
                            "      ,GoodsNumber,ManualNumber,ConsignmentBillDate,Consignee,ConsigneeMobileTelephone,ConsigneeTelephone,Consignor,ConsignorMobileTelephone,\n" +
                            "      ConsignorTelephone,SendCompany,ReceiveCompany from CollectionGoodsValueAccount as a WITH(NOLOCK) left join (select ConsignmentBillMasterId \n" +
                            "     ,GoodsNumber,ManualNumber,ConsignmentBillDate,Consignee,ConsigneeMobileTelephone,ConsigneeTelephone,Consignor,ConsignorMobileTelephone,\n" +
                            "           ConsignorTelephone,SendCompany,ReceiveCompany from ConsignmentBillMaster WITH(NOLOCK)) Consi on a.ConsignmentBillMasterId=Consi.ConsignmentBillMasterId\n" +
                            "left join Sys_Company c on a.OrganizationCode=c.OrganizationCode AND Consi.sendCompany=c.CompanyName "+
                            "      where ConsignmentBillDate>='"+CreateTimeBegin+"' AND ConsignmentBillDate<='"+CreateTimeEnd+"' AND a.OrganizationCode='"+OrganizationCode+"'";
                    List<CollectiongoodsvalueaccountPublic> listByConsignmentBillMaster = jdbcDao.queryListForSql(seleclSql, CollectiongoodsvalueaccountPublic.class);
                    String numSql="";
                    int num=0;
                    for (CollectiongoodsvalueaccountPublic car : listByConsignmentBillMaster) {
                        try{
                            ResultModel reModel= jrptExecutor.uploadCollection(SystemConstants.CAR_URL,car);
                            if (100==reModel.getCode()){
                                num=num+1;
                            }else{
//                                car = (CollectiongoodsvalueaccountPublic)killNull(car);
                                car.setTrackingno("补传失败"+reModel.getMessage());
                                car.setFlag("0");
                                jdbcDao_GoodsTracked.insert(car);
                            }
                        }catch (Exception e){
                            log.error("CollectiongoodsvalueaccountPublic error:",e);
                        }

                    }
                    try{
                        if (num>0){
                            numSql="insert into bigdata(type,num,uploaddate,tenant) values ('dshk',"+num+",'"+CreateTimeBegin+"','"+OrganizationCode+"')" ;
                            jdbcDao_GoodsTracked.updateForSql(numSql);
                        }
                    }catch (Exception e){
                        log.error(" bigdata dshk error",e);
                    }
                    end = System.currentTimeMillis();
                    log.info("CreateTimeBegin:"+CreateTimeBegin+" CreateTimeEnd:"+CreateTimeEnd+" OrganizationCode:"+OrganizationCode+" DataSource:" + dataSource + ";Databasename:" + databasename + ";代收数据：" + listByConsignmentBillMaster.size() + ";消耗" + (end - begin) + "豪秒。");
                }
            }

        }catch (Exception e){
            log.error("dshksave error",e);
        }
        }
    @Override
    public void insert_order_buchuan(String dataSource, String databasename, String wxpublicnumber, String appid
            ,String CreateTimeBegin,String CreateTimeEnd,String OrganizationCode) {
        if (wxpublicnumber == null) {
            wxpublicnumber = "";
        }
        if (appid == null) {
            appid = "";
        }
        String sqlArray0 = "";
        List<GoodsTrack> listByDelete = new ArrayList<GoodsTrack>();
        Long begin;
        String seleclSql;
        Long end;
        begin = System.currentTimeMillis();

        try{ String tsql="c.FyCloudCode consignorCompanySende, " +
                "dh.FyCloudCode consigneeCompanySenderCode," +
                "c.Longitude ConsignmentLongitude," +
                "c.Latitude ConsignmentLatitude," +
                "dh.Longitude ArrivalLongitude," +
                "dh.Latitude ArrivalLatitude," +
//                "CONVERT(varchar(200),a.SendLongitude) SendLongitude," +
//                "CONVERT(varchar(200),a.SendLatitude) SendLatitude," +
//                "a.PickAddress PickAddress," +
//                "CONVERT(varchar(200),a.ReceiveLongitude) ReceiveLongitude," +
//                " CONVERT(varchar(200),a.ReceiveLatitude) ReceiveLatitude," +
                "z.FyCloudCode Headquarterscode," +
                "z.FyCloudPassword headquarterspassword," +
                "dh.FyCloudCode arrivalcode," +
                "dh.FyCloudPassword arrivalpassword," +
                "c.FyCloudCode consignmentcode," +
                "c.FyCloudPassword consignmentpassword,";
            List<Jrpt> jrpts = jrptService.select();
            for (Jrpt jrpt : jrpts) {
                if(jrpt!=null&&jrpt.getOrganizationcode()!=null&&jrpt.getOrganizationcode().length()!=0){
                    OrganizationCode=jrpt.getOrganizationcode();
                    seleclSql=" select "+tsql+" a.OrganizationCode,ConsignmentBillMasterId,ConsignmentBillNumber, Consignoridcard,Consigneeidcard, \n" +
                            "       GoodsNumber,ManualNumber, ConsignmentBillDate,PlaceOfLoading,SendCompany,ReceiveCompany,UnloadPlace,ConsignorCompanyCode, ConsignorCompany,\n" +
                            "       Consignor, ConsignorMobileTelephone,ConsignorTelephone, ConsignorAddress,ConsigneeCompanyCode,ConsigneeCompany,\n" +
                            "       Consignee,ConsigneeMobileTelephone,ConsigneeTelephone,ConsigneeAddress,OneCardCode,BankName,AccountHolder,\n" +
                            "       Account,PaymentMethod,GoodsPickupMethod,ConsignmentBillRemark,GoodsName,GoodsPack, IsReceipt,ReceiptNumber,ReceiptCount,ReceiptRemark,\n" +
                            "       TotalNumberOfPackages,TotalWeight,TotalVolume,TotalTransportCost, CollectionGoodsValue,AdvanceGoodsValue, AdvanceTransportCost,\n" +
                            "     0 ,GETDATE() SysTime ,ISNULL(c.Longitude,'0')SendLongitude,ISNULL(c.Latitude,'0')SendLatitude,''PickAddress,ISNULL(dh.Longitude,'0')ReceiveLongitude,\n" +
                            "     ISNULL(c.Latitude,'0')ReceiveLatitude,\n" +
                            " convert(varchar(20),convert(decimal(18,0),CASE WHEN OfflineUpload='App' THEN 1 WHEN  OfflineUpload='在线下单' THEN 2 ELSE 0 END)) CreateType "+
//                            "       CASE WHEN OfflineUpload='App' THEN 1 WHEN  OfflineUpload='在线下单' THEN 2 ELSE 0 END  CreateType\n" +
                            "       from ConsignmentBillMaster as a WITH(NOLOCK)\n" +
                            "    left join Sys_Company c WITH(NOLOCK) on a.OrganizationCode=c.OrganizationCode and a.SendCompany=c.CompanyName " +
                            "    left join Sys_Company dh WITH(NOLOCK) on a.OrganizationCode=dh.OrganizationCode and a.ReceiveCompany=dh.CompanyName " +
                            "    left join Sys_Company z WITH(NOLOCK) on a.OrganizationCode=z.OrganizationCode and z.CompanyType='总公司'"+
                            "       where ConsignmentBillDate>='"+CreateTimeBegin+"' AND ConsignmentBillDate<='"+CreateTimeEnd+"' \n" +
                            "         and c.OrganizationCode='"+OrganizationCode+"'  and dh.OrganizationCode='"+OrganizationCode+"' and a.OrganizationCode='"+OrganizationCode+"'";
                    List<GoodstrackedRecordTopPublicF6> listByConsignmentBillMaster = jdbcDao.queryListForSql(seleclSql, GoodstrackedRecordTopPublicF6.class);
                    String numSql="";
                    int num=0;

                    for (GoodstrackedRecordTopPublicF6 car : listByConsignmentBillMaster) {
                        try{
                            ResultModel reModel =jrptExecutor.uploadGoodsTrace(SystemConstants.CAR_URL,"",car);
                            if (reModel!=null&&100==reModel.getCode()){
                                num=num+1;
                            }else {
                                car = (GoodstrackedRecordTopPublicF6)killNull(car);
                                car.setTraceplatformid("补传失败"+reModel.getMessage());
                                jdbcDao_GoodsTracked.insert(car);
                            }
                        }catch (Exception e){
                            log.error("订单补传 error:",e);

                        }
                    }
                    try{
                        if (num>0){
                            numSql="insert into bigdata(type,num,uploaddate,tenant) values ('order',"+num+",'"+CreateTimeBegin+"','"+OrganizationCode+"')" ;
                            jdbcDao_GoodsTracked.updateForSql(numSql);
                        }
                    }catch (Exception e){
                        log.error(" bigdata order error",e);
                    }
                    end = System.currentTimeMillis();
                    log.info("CreateTimeBegin:"+CreateTimeBegin+" CreateTimeEnd:"+CreateTimeEnd+" OrganizationCode:"+OrganizationCode+" DataSource:" + dataSource + ";Databasename:" + databasename + ";订单：" + listByConsignmentBillMaster.size() + ";消耗" + (end - begin) + "豪秒。");

                }
            }
        }catch (Exception e){
            log.error(" order error:",e);
        }
        }
}
